/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package DBLayer;

import DBLayer.Interfaces.IContainer;
import ModelLayer.Car;
import ModelLayer.CarColor;
import ModelLayer.CarEngine;
import ModelLayer.Product;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import java.util.logging.Level;
import java.util.logging.Logger;

/**
 *
 * @author Evil
 */
public class DBProduct extends DatabaseAccess implements IContainer<Product>
{
    DBSupplier dbSupplier;
    // ArrayList<int[]> list = null;

    public DBProduct()
    {
        dbSupplier = new DBSupplier();
    }

    public Product getObject(int productID)
    {
        String query = "Select ProductID as ID, MinStock, MaxStock, Quantity, Description, SupplierID, PurchasePrice, SalesPrice From Product Where Product.ProductID = " + productID;

        GenericModelBinder<Product> modelbinder = new GenericModelBinder<Product>(Product.class);
        try
        {
            ResultSet results = ExecuteQuery(query);
            Product product = modelbinder.BindModel(results);
            product.setSupplier(dbSupplier.getObject(product.getSupplierID()));
            return product;
        }
        catch (SQLException ex)
        {
            Logger.getLogger(DBProduct.class.getName()).log(Level.SEVERE, null, ex);
        }
        catch (InstantiationException ex)
        {
            Logger.getLogger(DBProduct.class.getName()).log(Level.SEVERE, null, ex);
        }
        catch (IllegalAccessException ex)
        {
            Logger.getLogger(DBProduct.class.getName()).log(Level.SEVERE, null, ex);
        }

        return null;
    }

    public ArrayList<Product> getObjects()
    {
        ArrayList<Product> products = new ArrayList<Product>();
        GenericModelBinder<Product> modelBinder = new GenericModelBinder<Product>(Product.class);

        String query = "Select ProductID as ID, MinStock, MaxStock, Quantity, Description, SupplierID, PurchasePrice, SalesPrice From Product";

        try
        {
            products = modelBinder.BindModels(ExecuteQuery(query));

            for (Product product : products)
            {
                product.setSupplier(dbSupplier.getObject(product.getSupplierID()));
            }

            return products;
        }
        catch (SQLException ex)
        {
            Logger.getLogger(DBProduct.class.getName()).log(Level.SEVERE, null, ex);
        }
        catch (InstantiationException ex)
        {
            Logger.getLogger(DBProduct.class.getName()).log(Level.SEVERE, null, ex);
        }
        catch (IllegalAccessException ex)
        {
            Logger.getLogger(DBProduct.class.getName()).log(Level.SEVERE, null, ex);
        }
        return null;
    }

    public boolean SaveObject(Product object)
    {
        try
        {
            String sql = String.format("INSERT INTO Product Values (%1$s, %2$s, %3$s, '%4$s', %5$s, %6$s, %7$s)",
                    object.getMinStock(), object.getMaxStock(), object.getQuantity(), object.getDescription(), object.getSupplier().getSupplierID(), object.getPurchasePrice(), object.getSalesPrice());

            Statement statement = dbConnection.createStatement();
            statement.execute(sql, Statement.RETURN_GENERATED_KEYS);
            ResultSet generatedResult = statement.getGeneratedKeys();
            int generatedID = 0;
            if (generatedResult.next())
            {
                generatedID = generatedResult.getInt(1);
                object.setProductID(generatedID);
                if (object instanceof Car)
                {
                    Car carProduct = (Car)object;
                    carProduct.setProductID(generatedID);
                    return saveCar(carProduct);
                }
            }
            else
            {
                return false;
            }
        }
        catch (SQLException ex)
        {
            Logger.getLogger(DBProduct.class.getName()).log(Level.SEVERE, null, ex);
        }
        return false;
    }

    private boolean saveCar(Car car)
    {
        String sql = String.format("INSERT INTO Car VALUES ('%1$s', '%2$s', %3$s, %4$s, %5$s, %6$s, %7$s, '%8$s', '%9$s')",
                car.getBrand(), car.getModel(), car.getEngine(), car.getProductID(), car.getDoors(), car.getYear(), car.getHk(), car.getFuel(), car.getColor());

        int result = ExecuteNoResultQuery(sql);

        return result > 0;
    }

    public boolean DeleteObject(Product object)
    {
        throw new UnsupportedOperationException("Not supported yet.");
    }

    public boolean UpdateObject(Product object)
    {
        throw new UnsupportedOperationException("Not supported yet.");
    }

    public ArrayList<Car> getProductsCar(String query)
    {

        ResultSet result = ExecuteQuery(query);

        GenericModelBinder<Car> binder = new GenericModelBinder<Car>(Car.class);
        try
        {
            return binder.BindModels(result);
        }
        catch (SQLException ex)
        {
            Logger.getLogger(DBProduct.class.getName()).log(Level.SEVERE, null, ex);
        }
        catch (InstantiationException ex)
        {
            Logger.getLogger(DBProduct.class.getName()).log(Level.SEVERE, null, ex);
        }
        catch (IllegalAccessException ex)
        {
            Logger.getLogger(DBProduct.class.getName()).log(Level.SEVERE, null, ex);
        }
        return null;
    }

    public ArrayList<CarEngine> getCarEngine()
    {
        String query = "Select EngineID as ID, EngineSize, Fuel, HK, Price From EngineOpel";
        ResultSet result = ExecuteQuery(query);

        GenericModelBinder<CarEngine> binder = new GenericModelBinder<CarEngine>(CarEngine.class);
        try
        {
            return binder.BindModels(result);
        }
        catch (SQLException ex)
        {
            Logger.getLogger(DBProduct.class.getName()).log(Level.SEVERE, null, ex);
        }
        catch (InstantiationException ex)
        {
            Logger.getLogger(DBProduct.class.getName()).log(Level.SEVERE, null, ex);
        }
        catch (IllegalAccessException ex)
        {
            Logger.getLogger(DBProduct.class.getName()).log(Level.SEVERE, null, ex);
        }
        return null;
    }

    public ArrayList<CarColor> getCarColor()
    {
        String query = "Select ColorID as ID, Color, Price From ColorOpel";
        ResultSet result = ExecuteQuery(query);

        GenericModelBinder<CarColor> binder = new GenericModelBinder<CarColor>(CarColor.class);
        try
        {
            return binder.BindModels(result);
        }
        catch (SQLException ex)
        {
            Logger.getLogger(DBProduct.class.getName()).log(Level.SEVERE, null, ex);
        }
        catch (InstantiationException ex)
        {
            Logger.getLogger(DBProduct.class.getName()).log(Level.SEVERE, null, ex);
        }
        catch (IllegalAccessException ex)
        {
            Logger.getLogger(DBProduct.class.getName()).log(Level.SEVERE, null, ex);
        }
        return null;
    }

    public List<Integer> getDistinctCarYears() 
    {
        String query = "Select Distinct Car.Year From Car";

            ResultSet results = ExecuteQuery(query);
            try
            {
                assert results.getMetaData().getColumnCount() == 1;
                List<Integer> ints = new ArrayList<Integer>();
                while(results.next())
                {
                    ints.add(Integer.valueOf(results.getInt(1)));
                }
                return ints;
            }
            catch(SQLException ex)
            {
                System.out.println("Cannot get car years");
            }
            return null;
    }
}
